Larger Excel documents don't just contain data on single worksheets but perhaps can contain data across
multiple worksheets open in front of you.
I've got the example file that comes with this course it's called Excel 102 exercises and I'm currently
looking at the summary worksheet.
You can see down below this summary worksheet has got a simple little table to Columns A town and a
circulation circulation currently being empty.
The data that I want to fill into the circulation column comes from the three sheets to the left 2013
contains a very similar table town's circulation numbers.
Same with 2014 and same with 2015.
All the same towns but different circulations for different years.
So now what I want to do here with all of this data is summarize it all into one spot right here on
the circulation or excuse me on the Summary tab.
Now we're going to actually see this done with two methods.
The first one we're going to utilize a technique here called 3-D formulas.
Essentially we're going to create a formula that goes to other worksheets.
Typically when you think of summing up a group of numbers you just think of create a formula on one
worksheet and referencing cells on that same worksheet.
While here we're going to perform a mathematical operation just do some addition but we're going to
reference cells in different worksheets.
This is a 3-D reference or a 3-D formula.
Take a look.
So I'm currently on the Summary sheet.
I'm going to go up to sell before this is where I want to create the calculation I want to sum up the
circulation numbers for the town of Beverly so I'll go to before I mean to create a formula.
What do you start formulas with.
I mean a in equals and then I'm going to click on 2013 and if I look up at the top and my formula bar
I can now see equals 2013 and it's super excited it's going next my smart.
I'm now referencing within my formula the sheet called 2013.
Now what do I want to get from this sheet.
Well I want to get the circulation for Beverly so I'll go to before.
There it is.
I'm referencing worksheet 2013 cell before.
Now what do I want to do.
What type of math am I performing here.
Well I want to do some addition.
So I mean hit the plus side now I need to get the next bit of data so I need to go to 2014.
Look here my formula bar I'm now referencing sheet 2014 and I'll get sell before which is a circulation
from Beverly and I need to do this one more time in my plus line.
I'm going to go to 2015.
Grab Beverley's circulation.
Got my complete formula now.
2013 14 and 15.
All cells before I'm done.
I mean my enter key.
I'm back on the summary sheet and I've got my grand total for all three years for the town of Beverly
.
What is that.
Three million two hundred forty five thousand nine hundred ninety eight.
A simple calculation just branching across multiple worksheets.
Try this out.
Create a formula on the Summary sheet.
Got to sell before typing the equal sign and then it's just click click on 2013 grab the proper cell
hit your plus sign because you're doing some addition.
Go to 2014 grab the proper cell and continue the process.
Try getting it for each of the towns here.
Practice it a little bit just get it comfortable.
Build in formulas that span across multiple worksheets.